{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "_cell_guid": "b1076dfc-b9ad-4769-8c92-a6c4dae69d19",
    "_uuid": "8f2839f25d086af736a60e9eeb907d3b93b6e0e5"
   },
   "outputs": [],
   "source": [
    "\n",
    "import os\n",
    "import json\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from pandas.io.json import json_normalize\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "color = sns.color_palette()\n",
    "from sklearn.model_selection import KFold,cross_val_score\n",
    "%matplotlib inline\n",
    "\n",
    "from sklearn.metrics import mean_squared_error\n",
    "\n",
    "from sklearn import model_selection, preprocessing, metrics\n",
    "from sklearn.feature_extraction.text import TfidfVectorizer, CountVectorizer\n",
    "import lightgbm as lgb\n",
    "\n",
    "from sklearn.ensemble import  RandomForestRegressor, ExtraTreesRegressor, GradientBoostingRegressor\n",
    "from lightgbm import LGBMRegressor\n",
    "from xgboost import XGBRegressor\n",
    "from sklearn.linear_model import LinearRegression, Ridge,RidgeCV,LarsCV,LassoCV,ElasticNetCV,LassoLarsCV\n",
    "#from pystacknet.pystacknet import StackNetRegressor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "_uuid": "2a990854030d60354b14d7f53da83eb78d038b44"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(12690, 9) (4231, 8)\n"
     ]
    }
   ],
   "source": [
    "train = pd.read_excel('input/Data_Train.xlsx')\n",
    "test = pd.read_excel('input/Data_Test.xlsx')\n",
    "\n",
    "print(train.shape,test.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "_uuid": "97f23ee8205e04e991804d9a1eab75e2a8f6485c"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TITLE</th>\n",
       "      <th>RESTAURANT_ID</th>\n",
       "      <th>CUISINES</th>\n",
       "      <th>TIME</th>\n",
       "      <th>CITY</th>\n",
       "      <th>LOCALITY</th>\n",
       "      <th>RATING</th>\n",
       "      <th>VOTES</th>\n",
       "      <th>COST</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CASUAL DINING</td>\n",
       "      <td>9438</td>\n",
       "      <td>Malwani, Goan, North Indian</td>\n",
       "      <td>11am – 4pm, 7:30pm – 11:30pm (Mon-Sun)</td>\n",
       "      <td>Thane</td>\n",
       "      <td>Dombivali East</td>\n",
       "      <td>3.6</td>\n",
       "      <td>49 votes</td>\n",
       "      <td>1200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CASUAL DINING,BAR</td>\n",
       "      <td>13198</td>\n",
       "      <td>Asian, Modern Indian, Japanese</td>\n",
       "      <td>6pm – 11pm (Mon-Sun)</td>\n",
       "      <td>Chennai</td>\n",
       "      <td>Ramapuram</td>\n",
       "      <td>4.2</td>\n",
       "      <td>30 votes</td>\n",
       "      <td>1500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CASUAL DINING</td>\n",
       "      <td>10915</td>\n",
       "      <td>North Indian, Chinese, Biryani, Hyderabadi</td>\n",
       "      <td>11am – 3:30pm, 7pm – 11pm (Mon-Sun)</td>\n",
       "      <td>Chennai</td>\n",
       "      <td>Saligramam</td>\n",
       "      <td>3.8</td>\n",
       "      <td>221 votes</td>\n",
       "      <td>800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>QUICK BITES</td>\n",
       "      <td>6346</td>\n",
       "      <td>Tibetan, Chinese</td>\n",
       "      <td>11:30am – 1am (Mon-Sun)</td>\n",
       "      <td>Mumbai</td>\n",
       "      <td>Bandra West</td>\n",
       "      <td>4.1</td>\n",
       "      <td>24 votes</td>\n",
       "      <td>800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DESSERT PARLOR</td>\n",
       "      <td>15387</td>\n",
       "      <td>Desserts</td>\n",
       "      <td>11am – 1am (Mon-Sun)</td>\n",
       "      <td>Mumbai</td>\n",
       "      <td>Lower Parel</td>\n",
       "      <td>3.8</td>\n",
       "      <td>165 votes</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               TITLE  RESTAURANT_ID  \\\n",
       "0      CASUAL DINING           9438   \n",
       "1  CASUAL DINING,BAR          13198   \n",
       "2      CASUAL DINING          10915   \n",
       "3        QUICK BITES           6346   \n",
       "4     DESSERT PARLOR          15387   \n",
       "\n",
       "                                     CUISINES  \\\n",
       "0                 Malwani, Goan, North Indian   \n",
       "1              Asian, Modern Indian, Japanese   \n",
       "2  North Indian, Chinese, Biryani, Hyderabadi   \n",
       "3                            Tibetan, Chinese   \n",
       "4                                    Desserts   \n",
       "\n",
       "                                     TIME     CITY        LOCALITY RATING  \\\n",
       "0  11am – 4pm, 7:30pm – 11:30pm (Mon-Sun)    Thane  Dombivali East    3.6   \n",
       "1                    6pm – 11pm (Mon-Sun)  Chennai       Ramapuram    4.2   \n",
       "2     11am – 3:30pm, 7pm – 11pm (Mon-Sun)  Chennai      Saligramam    3.8   \n",
       "3                 11:30am – 1am (Mon-Sun)   Mumbai     Bandra West    4.1   \n",
       "4                    11am – 1am (Mon-Sun)   Mumbai     Lower Parel    3.8   \n",
       "\n",
       "       VOTES  COST  \n",
       "0   49 votes  1200  \n",
       "1   30 votes  1500  \n",
       "2  221 votes   800  \n",
       "3   24 votes   800  \n",
       "4  165 votes   300  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#train.TITLE.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "_uuid": "e2c45dc4144c878d7d4cc07f0e4237b891d05430"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TITLE</th>\n",
       "      <th>RESTAURANT_ID</th>\n",
       "      <th>CUISINES</th>\n",
       "      <th>TIME</th>\n",
       "      <th>CITY</th>\n",
       "      <th>LOCALITY</th>\n",
       "      <th>RATING</th>\n",
       "      <th>VOTES</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CASUAL DINING</td>\n",
       "      <td>4085</td>\n",
       "      <td>North Indian, Chinese, Mughlai, Kebab</td>\n",
       "      <td>12noon – 12midnight (Mon-Sun)</td>\n",
       "      <td>Noida</td>\n",
       "      <td>Sector 18</td>\n",
       "      <td>4.3</td>\n",
       "      <td>564 votes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>QUICK BITES</td>\n",
       "      <td>12680</td>\n",
       "      <td>South Indian, Fast Food, Pizza, North Indian</td>\n",
       "      <td>7am – 12:30AM (Mon-Sun)</td>\n",
       "      <td>Mumbai</td>\n",
       "      <td>Grant Road</td>\n",
       "      <td>4.2</td>\n",
       "      <td>61 votes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CASUAL DINING</td>\n",
       "      <td>1411</td>\n",
       "      <td>North Indian, Seafood, Biryani, Chinese</td>\n",
       "      <td>11am – 11:30pm (Mon-Sun)</td>\n",
       "      <td>Mumbai</td>\n",
       "      <td>Marine Lines</td>\n",
       "      <td>3.8</td>\n",
       "      <td>350 votes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>None</td>\n",
       "      <td>204</td>\n",
       "      <td>Biryani</td>\n",
       "      <td>9am – 10pm (Mon, Wed, Thu, Fri, Sat, Sun), 10:...</td>\n",
       "      <td>Faridabad</td>\n",
       "      <td>NIT</td>\n",
       "      <td>3.8</td>\n",
       "      <td>1445 votes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>QUICK BITES</td>\n",
       "      <td>13453</td>\n",
       "      <td>South Indian, Kerala</td>\n",
       "      <td>11am – 10pm (Mon-Sun)</td>\n",
       "      <td>Kochi</td>\n",
       "      <td>Kaloor</td>\n",
       "      <td>3.6</td>\n",
       "      <td>23 votes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           TITLE  RESTAURANT_ID                                      CUISINES  \\\n",
       "0  CASUAL DINING           4085         North Indian, Chinese, Mughlai, Kebab   \n",
       "1    QUICK BITES          12680  South Indian, Fast Food, Pizza, North Indian   \n",
       "2  CASUAL DINING           1411       North Indian, Seafood, Biryani, Chinese   \n",
       "3           None            204                                       Biryani   \n",
       "4    QUICK BITES          13453                          South Indian, Kerala   \n",
       "\n",
       "                                                TIME       CITY      LOCALITY  \\\n",
       "0                      12noon – 12midnight (Mon-Sun)      Noida     Sector 18   \n",
       "1                            7am – 12:30AM (Mon-Sun)     Mumbai    Grant Road   \n",
       "2                           11am – 11:30pm (Mon-Sun)     Mumbai  Marine Lines   \n",
       "3  9am – 10pm (Mon, Wed, Thu, Fri, Sat, Sun), 10:...  Faridabad           NIT   \n",
       "4                              11am – 10pm (Mon-Sun)      Kochi        Kaloor   \n",
       "\n",
       "  RATING       VOTES  \n",
       "0    4.3   564 votes  \n",
       "1    4.2    61 votes  \n",
       "2    3.8   350 votes  \n",
       "3    3.8  1445 votes  \n",
       "4    3.6    23 votes  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.feature_extraction.text import TfidfVectorizer\n",
    "tf = TfidfVectorizer(ngram_range=(1, 1), lowercase=False)\n",
    "train_route = tf.fit_transform(train['TITLE'])\n",
    "test_route = tf.transform(test['TITLE'])\n",
    "\n",
    "train_route = pd.DataFrame(data=train_route.toarray(), columns=tf.get_feature_names())\n",
    "test_route = pd.DataFrame(data=test_route.toarray(), columns=tf.get_feature_names())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((12690, 30), (4231, 30))"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_route.shape,test_route.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "BAKERY            471.514829\n",
       "BAR               549.623754\n",
       "BEVERAGE          175.011514\n",
       "BITES            3108.106127\n",
       "CAFE                2.070268\n",
       "CAFÉ              772.989780\n",
       "CASUAL           2927.241057\n",
       "CLUB               26.852224\n",
       "COCKTAIL            5.331183\n",
       "CONFECTIONERY       4.629156\n",
       "COURT              75.851780\n",
       "DESSERT           402.309869\n",
       "DHABA              45.000000\n",
       "DINING           2954.002643\n",
       "FINE              317.793569\n",
       "FOOD               91.721114\n",
       "IRANI               2.070268\n",
       "KIOSK              58.926850\n",
       "LOUNGE            181.051354\n",
       "MEAT                2.627791\n",
       "MESS               53.000000\n",
       "MICROBREWERY       61.264162\n",
       "None             1003.000000\n",
       "PAAN                4.329635\n",
       "PARLOR            402.309869\n",
       "PUB               122.173260\n",
       "QUICK            3108.106127\n",
       "SHOP              251.839552\n",
       "SWEET             111.050401\n",
       "TRUCK              24.303777\n",
       "dtype: float64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_route.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((12690, 131), (4231, 131))"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sklearn.feature_extraction.text import TfidfVectorizer\n",
    "tf = TfidfVectorizer(ngram_range=(1, 1), lowercase=False)\n",
    "train_CUISINES = tf.fit_transform(train['CUISINES'])\n",
    "test_CUISINES = tf.transform(test['CUISINES'])\n",
    "\n",
    "train_CUISINES = pd.DataFrame(data=train_CUISINES.toarray(), columns=tf.get_feature_names())\n",
    "test_CUISINES = pd.DataFrame(data=test_CUISINES.toarray(), columns=tf.get_feature_names())\n",
    "\n",
    "train_CUISINES.shape,test_CUISINES.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((12690, 90), (4231, 90))"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sklearn.feature_extraction.text import TfidfVectorizer\n",
    "tf = TfidfVectorizer(ngram_range=(1, 1), lowercase=False)\n",
    "train_TIME = tf.fit_transform(train['TIME'])\n",
    "test_TIME = tf.transform(test['TIME'])\n",
    "\n",
    "train_TIME = pd.DataFrame(data=train_TIME.toarray(), columns=tf.get_feature_names())\n",
    "test_TIME = pd.DataFrame(data=test_TIME.toarray(), columns=tf.get_feature_names())\n",
    "\n",
    "train_TIME.shape,test_TIME.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "TITLE               0\n",
       "RESTAURANT_ID       0\n",
       "CUISINES            0\n",
       "TIME                0\n",
       "CITY              112\n",
       "LOCALITY           98\n",
       "RATING              2\n",
       "VOTES            1204\n",
       "COST                0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "TITLE              0\n",
       "RESTAURANT_ID      0\n",
       "CUISINES           0\n",
       "TIME               0\n",
       "CITY              35\n",
       "LOCALITY          30\n",
       "RATING             2\n",
       "VOTES            402\n",
       "dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "train['RATING'].fillna('NEW',inplace=True)\n",
    "test['RATING'].fillna('NEW',inplace=True)\n",
    "train['CITY'].fillna('None',inplace=True)\n",
    "test['CITY'].fillna('None',inplace=True)\n",
    "\n",
    "train['LOCALITY'].fillna('None',inplace=True)\n",
    "test['LOCALITY'].fillna('None',inplace=True)\n",
    "\n",
    "train['VOTES'].fillna('-9999 votes',inplace=True)\n",
    "test['VOTES'].fillna('-9999 votes',inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "train['RATING'] = train['RATING'].apply(lambda x : 0.00001 if (x == 'NEW') | (x == '-') else float(x))\n",
    "test['RATING'] = test['RATING'].apply(lambda x : 0.00001 if (x == 'NEW') | (x == '-') else float(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "train['votes'] = train['VOTES'].apply(lambda x : int(x.split()[0]))\n",
    "test['votes'] = test['VOTES'].apply(lambda x : int(x.split()[0]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "train.drop('VOTES',axis=1,inplace=True)\n",
    "test.drop('VOTES',axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "train['city_loc'] = train['CITY'] + train['LOCALITY']\n",
    "test['city_loc'] = test['CITY'] + test['LOCALITY']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "cat_cols = ['CITY','LOCALITY','city_loc']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CITY 360 152\n",
      "LOCALITY 1417 835\n",
      "city_loc 1663 934\n"
     ]
    }
   ],
   "source": [
    "# Impute 0 for missing target values\n",
    "train_df = train.copy()\n",
    "test_df = test.copy()\n",
    "\n",
    "# label encode the categorical variables and convert the numerical variables to float\n",
    "\n",
    "for col in cat_cols:\n",
    "    print(col,train_df[col].nunique(),test_df[col].nunique())\n",
    "    lbl = preprocessing.LabelEncoder()\n",
    "    lbl.fit(list(train_df[col].values.astype('str')) + list(test_df[col].values.astype('str')))\n",
    "    train_df[col] = lbl.transform(list(train_df[col].values.astype('str')))\n",
    "    test_df[col] = lbl.transform(list(test_df[col].values.astype('str')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((152, 10), (360, 10))"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#  categorical features\n",
    "num_aggregations = {}\n",
    "for num in [\"votes\", \"RATING\"]:\n",
    "    num_aggregations[num] = [ 'max', 'mean','std','median','min']\n",
    "\n",
    "supplier_id_train = train_df.groupby('CITY').agg({**num_aggregations})\n",
    "supplier_id_train.columns = pd.Index(['SID_' + e[0] + \"_\" + e[1].upper() for e in supplier_id_train.columns.tolist()])\n",
    "\n",
    "supplier_id_test = test_df.groupby('CITY').agg({**num_aggregations})\n",
    "supplier_id_test.columns = pd.Index(['SID_' + e[0] + \"_\" + e[1].upper() for e in supplier_id_test.columns.tolist()])\n",
    "\n",
    "supplier_id_test.shape,supplier_id_train.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((12690, 20), (4231, 19))"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df = pd.merge(train_df,supplier_id_train,on='CITY',how='left')\n",
    "test_df = pd.merge(test_df,supplier_id_test,on='CITY',how='left')\n",
    "train_df.shape,test_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((835, 10), (1417, 10))"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#  categorical features\n",
    "num_aggregations = {}\n",
    "for num in [\"votes\", \"RATING\"]:\n",
    "    num_aggregations[num] = [ 'max', 'mean','std','median','min']\n",
    "\n",
    "supplier_id_train = train_df.groupby('LOCALITY').agg({**num_aggregations})\n",
    "supplier_id_train.columns = pd.Index(['SID_' + e[0] + \"_\" + e[1].upper() for e in supplier_id_train.columns.tolist()])\n",
    "\n",
    "supplier_id_test = test_df.groupby('LOCALITY').agg({**num_aggregations})\n",
    "supplier_id_test.columns = pd.Index(['SID_' + e[0] + \"_\" + e[1].upper() for e in supplier_id_test.columns.tolist()])\n",
    "\n",
    "supplier_id_test.shape,supplier_id_train.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((12690, 30), (4231, 29))"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df = pd.merge(train_df,supplier_id_train,on='LOCALITY',how='left')\n",
    "test_df = pd.merge(test_df,supplier_id_test,on='LOCALITY',how='left')\n",
    "train_df.shape,test_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TITLE</th>\n",
       "      <th>RESTAURANT_ID</th>\n",
       "      <th>CUISINES</th>\n",
       "      <th>TIME</th>\n",
       "      <th>CITY</th>\n",
       "      <th>LOCALITY</th>\n",
       "      <th>RATING</th>\n",
       "      <th>COST</th>\n",
       "      <th>votes</th>\n",
       "      <th>city_loc</th>\n",
       "      <th>...</th>\n",
       "      <th>SID_votes_MAX_y</th>\n",
       "      <th>SID_votes_MEAN_y</th>\n",
       "      <th>SID_votes_STD_y</th>\n",
       "      <th>SID_votes_MEDIAN_y</th>\n",
       "      <th>SID_votes_MIN_y</th>\n",
       "      <th>SID_RATING_MAX_y</th>\n",
       "      <th>SID_RATING_MEAN_y</th>\n",
       "      <th>SID_RATING_STD_y</th>\n",
       "      <th>SID_RATING_MEDIAN_y</th>\n",
       "      <th>SID_RATING_MIN_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CASUAL DINING</td>\n",
       "      <td>9438</td>\n",
       "      <td>Malwani, Goan, North Indian</td>\n",
       "      <td>11am – 4pm, 7:30pm – 11:30pm (Mon-Sun)</td>\n",
       "      <td>401</td>\n",
       "      <td>328</td>\n",
       "      <td>3.6</td>\n",
       "      <td>1200</td>\n",
       "      <td>49</td>\n",
       "      <td>1897</td>\n",
       "      <td>...</td>\n",
       "      <td>532</td>\n",
       "      <td>-912.407407</td>\n",
       "      <td>3278.664670</td>\n",
       "      <td>99.0</td>\n",
       "      <td>-9999</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.281483</td>\n",
       "      <td>1.198405</td>\n",
       "      <td>3.60</td>\n",
       "      <td>0.00001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CASUAL DINING,BAR</td>\n",
       "      <td>13198</td>\n",
       "      <td>Asian, Modern Indian, Japanese</td>\n",
       "      <td>6pm – 11pm (Mon-Sun)</td>\n",
       "      <td>75</td>\n",
       "      <td>1215</td>\n",
       "      <td>4.2</td>\n",
       "      <td>1500</td>\n",
       "      <td>30</td>\n",
       "      <td>469</td>\n",
       "      <td>...</td>\n",
       "      <td>571</td>\n",
       "      <td>-335.369565</td>\n",
       "      <td>2085.208483</td>\n",
       "      <td>79.0</td>\n",
       "      <td>-9999</td>\n",
       "      <td>4.3</td>\n",
       "      <td>3.419566</td>\n",
       "      <td>0.836026</td>\n",
       "      <td>3.60</td>\n",
       "      <td>0.00001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CASUAL DINING</td>\n",
       "      <td>10915</td>\n",
       "      <td>North Indian, Chinese, Biryani, Hyderabadi</td>\n",
       "      <td>11am – 3:30pm, 7pm – 11pm (Mon-Sun)</td>\n",
       "      <td>75</td>\n",
       "      <td>1273</td>\n",
       "      <td>3.8</td>\n",
       "      <td>800</td>\n",
       "      <td>221</td>\n",
       "      <td>474</td>\n",
       "      <td>...</td>\n",
       "      <td>221</td>\n",
       "      <td>113.142857</td>\n",
       "      <td>73.777658</td>\n",
       "      <td>91.0</td>\n",
       "      <td>27</td>\n",
       "      <td>3.8</td>\n",
       "      <td>3.442857</td>\n",
       "      <td>0.390969</td>\n",
       "      <td>3.50</td>\n",
       "      <td>2.70000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>QUICK BITES</td>\n",
       "      <td>6346</td>\n",
       "      <td>Tibetan, Chinese</td>\n",
       "      <td>11:30am – 1am (Mon-Sun)</td>\n",
       "      <td>277</td>\n",
       "      <td>161</td>\n",
       "      <td>4.1</td>\n",
       "      <td>800</td>\n",
       "      <td>24</td>\n",
       "      <td>1246</td>\n",
       "      <td>...</td>\n",
       "      <td>2883</td>\n",
       "      <td>-553.446429</td>\n",
       "      <td>3364.887457</td>\n",
       "      <td>211.5</td>\n",
       "      <td>-9999</td>\n",
       "      <td>4.6</td>\n",
       "      <td>3.628573</td>\n",
       "      <td>1.308252</td>\n",
       "      <td>4.05</td>\n",
       "      <td>0.00001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DESSERT PARLOR</td>\n",
       "      <td>15387</td>\n",
       "      <td>Desserts</td>\n",
       "      <td>11am – 1am (Mon-Sun)</td>\n",
       "      <td>277</td>\n",
       "      <td>709</td>\n",
       "      <td>3.8</td>\n",
       "      <td>300</td>\n",
       "      <td>165</td>\n",
       "      <td>1308</td>\n",
       "      <td>...</td>\n",
       "      <td>5724</td>\n",
       "      <td>1044.203125</td>\n",
       "      <td>1073.743063</td>\n",
       "      <td>765.0</td>\n",
       "      <td>13</td>\n",
       "      <td>4.9</td>\n",
       "      <td>4.093750</td>\n",
       "      <td>0.331842</td>\n",
       "      <td>4.10</td>\n",
       "      <td>3.40000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               TITLE  RESTAURANT_ID  \\\n",
       "0      CASUAL DINING           9438   \n",
       "1  CASUAL DINING,BAR          13198   \n",
       "2      CASUAL DINING          10915   \n",
       "3        QUICK BITES           6346   \n",
       "4     DESSERT PARLOR          15387   \n",
       "\n",
       "                                     CUISINES  \\\n",
       "0                 Malwani, Goan, North Indian   \n",
       "1              Asian, Modern Indian, Japanese   \n",
       "2  North Indian, Chinese, Biryani, Hyderabadi   \n",
       "3                            Tibetan, Chinese   \n",
       "4                                    Desserts   \n",
       "\n",
       "                                     TIME  CITY  LOCALITY  RATING  COST  \\\n",
       "0  11am – 4pm, 7:30pm – 11:30pm (Mon-Sun)   401       328     3.6  1200   \n",
       "1                    6pm – 11pm (Mon-Sun)    75      1215     4.2  1500   \n",
       "2     11am – 3:30pm, 7pm – 11pm (Mon-Sun)    75      1273     3.8   800   \n",
       "3                 11:30am – 1am (Mon-Sun)   277       161     4.1   800   \n",
       "4                    11am – 1am (Mon-Sun)   277       709     3.8   300   \n",
       "\n",
       "   votes  city_loc        ...         SID_votes_MAX_y  SID_votes_MEAN_y  \\\n",
       "0     49      1897        ...                     532       -912.407407   \n",
       "1     30       469        ...                     571       -335.369565   \n",
       "2    221       474        ...                     221        113.142857   \n",
       "3     24      1246        ...                    2883       -553.446429   \n",
       "4    165      1308        ...                    5724       1044.203125   \n",
       "\n",
       "   SID_votes_STD_y  SID_votes_MEDIAN_y  SID_votes_MIN_y  SID_RATING_MAX_y  \\\n",
       "0      3278.664670                99.0            -9999               4.0   \n",
       "1      2085.208483                79.0            -9999               4.3   \n",
       "2        73.777658                91.0               27               3.8   \n",
       "3      3364.887457               211.5            -9999               4.6   \n",
       "4      1073.743063               765.0               13               4.9   \n",
       "\n",
       "   SID_RATING_MEAN_y  SID_RATING_STD_y  SID_RATING_MEDIAN_y  SID_RATING_MIN_y  \n",
       "0           3.281483          1.198405                 3.60           0.00001  \n",
       "1           3.419566          0.836026                 3.60           0.00001  \n",
       "2           3.442857          0.390969                 3.50           2.70000  \n",
       "3           3.628573          1.308252                 4.05           0.00001  \n",
       "4           4.093750          0.331842                 4.10           3.40000  \n",
       "\n",
       "[5 rows x 30 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df.drop(['TITLE','CUISINES','TIME'],axis=1,inplace=True)\n",
    "test_df.drop(['TITLE','CUISINES','TIME'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((12690, 278), (4231, 277))"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df = pd.concat([train_df,train_route,train_TIME,train_CUISINES],axis=1)\n",
    "test_df = pd.concat([test_df,test_route,test_TIME,test_CUISINES],axis=1)\n",
    "\n",
    "train_df.shape,test_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "for col1, col2 in [\n",
    "                   [\"votes\", \"RATING\"],\n",
    "                   \n",
    "                  ]:\n",
    "    train_df[col1+\"_ratio_\"+col2] = train_df[col1] / train_df[col2]\n",
    "    test_df[col1+\"_ratio_\"+col2] = test_df[col1] / test_df[col2]\n",
    "    train_df[col1+\"_mul_\"+col2] = train_df[col1] * train_df[col2]\n",
    "    test_df[col1+\"_mul_\"+col2] = test_df[col1] * test_df[col2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df['id'] = range(train_df.shape[0])\n",
    "test_df['id'] = range(train_df.shape[0],train_df.shape[0] + test_df.shape[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "for col in [\n",
    "            \"RESTAURANT_ID\", [\"CITY\", \"LOCALITY\"],\n",
    "            [\"RESTAURANT_ID\", \"CITY\"],\n",
    "            [\"RESTAURANT_ID\", \"LOCALITY\"],\n",
    "            [\"RESTAURANT_ID\", \"city_loc\"],\n",
    "           ]:\n",
    "    if not isinstance(col, list):\n",
    "        col = [col]\n",
    "    col_name = \"_\".join(col)\n",
    "    all_df = pd.concat([train_df[[\"id\"]+ col], test_df[[\"id\"]+ col]])\n",
    "    gdf = all_df.groupby(col)[\"id\"].count().reset_index()\n",
    "    gdf.columns = col + [col_name+\"_count\"]\n",
    "    train_df = pd.merge(train_df, gdf, on=col, how=\"left\")\n",
    "    test_df = pd.merge(test_df, gdf, on=col, how=\"left\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "for col in [\n",
    "            \"RESTAURANT_ID\", [\"CITY\", \"LOCALITY\"],\n",
    "            [\"RESTAURANT_ID\", \"CITY\"],\n",
    "            [\"RESTAURANT_ID\", \"LOCALITY\"],\n",
    "            [\"RESTAURANT_ID\", \"city_loc\"],\n",
    "           ]:\n",
    "    if not isinstance(col, list):\n",
    "        col = [col]\n",
    "    col_name = \"_\".join(col)\n",
    "    all_df = pd.concat([train_df[[\"votes\"]+ col], test_df[[\"votes\"]+ col]])\n",
    "    gdf = all_df.groupby(col)[\"votes\"].mean().reset_index()\n",
    "    gdf.columns = col + [col_name+\"_vmean\"]\n",
    "    train_df = pd.merge(train_df, gdf, on=col, how=\"left\")\n",
    "    test_df = pd.merge(test_df, gdf, on=col, how=\"left\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df.drop('id',axis=1,inplace=True)\n",
    "test_df.drop('id',axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "_uuid": "74e23462bfdf6fa238422883c0b7dcf9a3b2e323"
   },
   "outputs": [],
   "source": [
    "train_y = np.log1p(train_df['COST']).values\n",
    "X = train_df.drop('COST',axis=1)\n",
    "y=np.log1p(train_df['COST'])\n",
    "X_test = test_df.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((12690, 289), (4231, 289))"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "X.shape,X_test.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "set()"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set(X.columns) - set(X_test.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "_uuid": "7903b14947884ebff7b2508fc920576ddb4e9592"
   },
   "outputs": [],
   "source": [
    "dev_X, val_X, dev_y, val_y = model_selection.train_test_split(X, y, test_size=0.25, random_state=42)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "_uuid": "137a569bae7961f1a14a8e146220d2301dea25ae"
   },
   "outputs": [],
   "source": [
    "random_seed = 2019\n",
    "k = 5\n",
    "fold = list(KFold(k, shuffle = True, random_state = random_seed).split(train))\n",
    "np.random.seed(random_seed)\n",
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(9517, 289) (3173, 289) (4231, 9) (3173,) (9517,)\n"
     ]
    }
   ],
   "source": [
    "print(dev_X.shape,val_X.shape,test.shape,val_y.shape,dev_y.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "# custom function to run light gbm model\n",
    "def run_lgb(train_X, train_y, val_X, val_y, test_X):\n",
    "    params = {\n",
    "        \"objective\" : \"regression\",\n",
    "        \"metric\" : \"rmse\", \n",
    "        \"num_leaves\" : 30,\n",
    "        \"min_child_samples\" : 100,\n",
    "        \"learning_rate\" : 0.1,\n",
    "        \"bagging_fraction\" : 0.7,\n",
    "        \"feature_fraction\" : 0.5,\n",
    "        \"bagging_frequency\" : 5,\n",
    "        \"bagging_seed\" : 2018,\n",
    "        \"verbosity\" : -1\n",
    "    }\n",
    "    \n",
    "    lgtrain = lgb.Dataset(train_X, label=train_y)\n",
    "    lgval = lgb.Dataset(val_X, label=val_y)\n",
    "    model = lgb.train(params, lgtrain, 1000, valid_sets=[lgval], early_stopping_rounds=100, verbose_eval=100)\n",
    "    \n",
    "    pred_test_y = model.predict(test_X, num_iteration=model.best_iteration)\n",
    "    pred_val_y = model.predict(val_X, num_iteration=model.best_iteration)\n",
    "    return pred_test_y, model, pred_val_y"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Training until validation scores don't improve for 100 rounds.\n",
      "[100]\tvalid_0's rmse: 0.358373\n",
      "[200]\tvalid_0's rmse: 0.357047\n",
      "Early stopping, best iteration is:\n",
      "[161]\tvalid_0's rmse: 0.356678\n"
     ]
    }
   ],
   "source": [
    "# Training the model #\n",
    "pred_test, model, pred_val = run_lgb(dev_X, dev_y, val_X, val_y, X_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "pred_test[pred_test<0] =0 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "test = pd.read_excel('input/Data_Test.xlsx')\n",
    "test['COST'] = np.round(np.expm1(pred_test))\n",
    "\n",
    "from pandas import ExcelWriter\n",
    "writer = ExcelWriter('MH_v3_lgb.xlsx')\n",
    "test[['COST']].to_excel(writer,'Sheet1')\n",
    "writer.save()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
